package com.xxl.job.admin.service.impl;

import com.xxl.excel.core.ExcelExportUtil;
import com.xxl.excel.core.ExeUtil;
import com.xxl.job.admin.dao.DbInfoDao;
import com.xxl.job.admin.service.DataCenterService;
import com.xxl.job.core.biz.model.DbInfo;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.constants.Constants;
import com.xxl.job.core.util.DateUtil;
import com.xxl.job.core.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.awt.*;
import java.io.File;
import java.util.*;
import java.util.List;

@Service
public class DataCenterServiceImpl implements DataCenterService {
    private Logger logger = LoggerFactory.getLogger(DataCenterServiceImpl.class);

    @Autowired
    private DbInfoDao dbInfoDao;

    @Value("${xxl.excel.exe_path}")
    private String excelExePath;

    /**
     * export table to excel
     *
     * @param id        DBinfo id
     * @param tableName table's name
     * @return
     */
    @Override
    public ReturnT<String> exportToFile(HttpServletRequest request, Long id, String tableName, String sheetName) {
        if(id == null) return ReturnT.FAIL;
        DbInfo dbInfo = dbInfoDao.loadById(id);
        if(dbInfo == null) return ReturnT.FAIL;

        if(Constants.DB_TYPE_MONGODB.equals(dbInfo.getDbType())) return ReturnT.FAIL;
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(dbInfo.getDbDriver());
        dataSource.setUrl(dbInfo.getDbUrl());
        dataSource.setUsername(dbInfo.getDbUserName());
        dataSource.setPassword(dbInfo.getDbPassword());
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        try {
            String basePath = request.getSession().getServletContext().getRealPath("/");
            String filePath = "";
            if(basePath.endsWith("/") || basePath.endsWith("\\")
                    || basePath.endsWith(File.separator)) {
                filePath = basePath + sheetName + DateUtil.format(new Date(), "yyyyMMDDHHmmss") + ".xlsx";
            } else {
                filePath = basePath + File.separator + sheetName + DateUtil.format(new Date(), "yyyyMMDDHHmmss") + ".xlsx";
            }
            //System.out.println(filePath);
            if(Constants.DB_TYPE_MYSQL.equals(dbInfo.getDbType())) {
                List<Map<String, Object>> dataList = jdbcTemplate.queryForList("select * from `" + tableName + "`;");;
                //List<Map<String, Object>> fieldList = jdbcTemplate.queryForList("show full fields from  " + tableName);
                List<Map<String, Object>> fieldList =
                        jdbcTemplate.queryForList("SELECT COLUMN_NAME 'field', CASE WHEN IFNULL(COLUMN_COMMENT,COLUMN_NAME)='' THEN COLUMN_NAME " +
                                "ELSE COLUMN_COMMENT END  'comment' FROM INFORMATION_SCHEMA.Columns WHERE table_name ='"+tableName+"';");
                ExcelExportUtil.exportToFile(filePath, sheetName, fieldList, dataList);

                //ExeUtil.openDirectory(filePath);
                //ExeUtil.exec(excelExePath + " " + filePath);
                // 打开文件：
                //File file = new File(filePath);
                //java.awt.Desktop.getDesktop().open(file);

                return new ReturnT<>(filePath);
                //System.out.println("DbInfoServiceImpl.exportToFile" + JSON.toJSONString(fieldList));

                //System.out.println("DbInfoServiceImpl.exportToFile" + JSON.toJSONString(dataList));
            }
        } catch (Exception e) {
            return ReturnT.FAIL;
        }
        return ReturnT.FAIL;
    }

    @Override
    public ReturnT<List<Map<String, Object>>> searchTableFields(Long id, String tableName) {
        if(id == null) return new ReturnT<>(ReturnT.FAIL_CODE, "");
        DbInfo dbInfo = dbInfoDao.loadById(id);
        if(dbInfo == null) return new ReturnT<>(ReturnT.FAIL_CODE, "");

        if(Constants.DB_TYPE_MONGODB.equals(dbInfo.getDbType())) return new ReturnT<>(ReturnT.FAIL_CODE, "");

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(dbInfo.getDbDriver());
        dataSource.setUrl(dbInfo.getDbUrl());
        dataSource.setUsername(dbInfo.getDbUserName());
        dataSource.setPassword(dbInfo.getDbPassword());
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        if(Constants.DB_TYPE_MYSQL.equals(dbInfo.getDbType())) {
            List<Map<String, Object>> fieldList =
                    jdbcTemplate.queryForList("SELECT COLUMN_NAME 'field', CASE WHEN IFNULL(COLUMN_COMMENT,COLUMN_NAME)='' THEN COLUMN_NAME " +
                            "ELSE COLUMN_COMMENT END  'comment' FROM INFORMATION_SCHEMA.Columns WHERE table_name ='"+tableName+"';");
            return new ReturnT<>(fieldList);
        }
        return new ReturnT<>(ReturnT.FAIL_CODE, "");
    }

    @Override
    public ReturnT<List<Map<String, Object>>> pageList(Long id, String tableName, int start, int length) {
        if(id == null) return new ReturnT<>(ReturnT.FAIL_CODE, "");
        DbInfo dbInfo = dbInfoDao.loadById(id);
        if(dbInfo == null) return new ReturnT<>(ReturnT.FAIL_CODE, "");

        if(Constants.DB_TYPE_MONGODB.equals(dbInfo.getDbType())) return new ReturnT<>(ReturnT.FAIL_CODE, "");

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(dbInfo.getDbDriver());
        dataSource.setUrl(dbInfo.getDbUrl());
        dataSource.setUsername(dbInfo.getDbUserName());
        dataSource.setPassword(dbInfo.getDbPassword());
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object count = jdbcTemplate.queryForObject("select count(*) from " + tableName, Object.class);
        Long list_count = (Long) count;

        //file_belongto,file_createtime,file_createuserid,file_createusername,file_data,file_ext,file_md5,file_name,file_path,file_save_type,file_size,file_state,file_type,id
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from " + tableName + " limit " + start + "," + length);
        System.out.println(list);
        // package result
        Map<String, Object> maps = new HashMap<>();
        maps.put("recordsTotal", list_count);		// 总记录数
        maps.put("recordsFiltered", list_count);	// 过滤后的总记录数
        maps.put("data", list);
        return null;
    }

    /**
     * open file
     *
     * @param filePath
     * @return
     */
    @Override
    public ReturnT<String> open(String filePath) {
        if(StringUtils.isBlank(filePath)) return ReturnT.FAIL;
        try {
            ExeUtil.exec(excelExePath + " " + filePath);
        } catch (Exception e) {
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    /**
     * @param filePath
     * @return
     */
    @Override
    public ReturnT<String> openDirectory(String filePath) {
        if(StringUtils.isBlank(filePath)) return ReturnT.FAIL;
        try {
            ExeUtil.openDirectory(filePath);
        } catch (Exception e) {
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }
}
